package cn.edu.sdjzu.xg.bysj.dao;


import cn.edu.sdjzu.xg.bysj.domain.Student;
import cn.edu.sdjzu.xg.bysj.domain.Teacher;
import cn.edu.sdjzu.xg.bysj.domain.authority.Actor;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;

import java.sql.*;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;

public final class ActorDao {
    private static ActorDao actorDao = new ActorDao();
    private static Collection<Teacher> actors;

    private ActorDao() {
    }

    public static ActorDao getInstance() {
        return actorDao;
    }

    public Collection<Teacher> findAll() throws SQLException {
        return ActorDao.actors;
    }

    public Collection<Student> findAll(Connection connection, List<Condition> conditionList, Pagination pagination) throws SQLException {
        //创建Teacher类型集合类对象，HashSet不保证结果中元素的顺序
        Collection<Student> desiredStudents = new HashSet<Student>();

        //创建查询教师的主语句
        StringBuilder sql_select = new StringBuilder("SELECT * FROM actor ");
        //如果有条件，则生成条件子句，并附加到主语句后面
        if (conditionList != null) {
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
        }
        //获取Teacher中的元组总数
        int totalNum = CommonDao.getInstance().count(connection, "Student");
        if (pagination != null) {
            String paginationClause = pagination.toLimitClause(totalNum);
            sql_select.append(paginationClause);
        }
        //获得查询教师的语句对象
        PreparedStatement pstmt_select =
                connection.prepareStatement(sql_select.toString());
        //获得教师对象的结果集
        ResultSet resultSet_select = pstmt_select.executeQuery();
        //遍历结果集，根据每个元素创建对象，并添加到集合类对象desiredTeachers中
        while (resultSet_select.next()) {
            int id = resultSet_select.getInt("id");
            String name = resultSet_select.getString("name");
            String no = resultSet_select.getString("no");
            String remarks = resultSet_select.getString("remarks");
            Student desiredStudent = new Student(name, no,remarks);
            desiredStudent.setId(id);
            desiredStudents.add(desiredStudent);
        }
        //关闭ResultSet, statement资源
        JdbcHelper.close(resultSet_select, pstmt_select);
        return desiredStudents;
    }

    public static Student find(Integer id, Connection connection) throws SQLException {
        //创建Teacher类型的引用，暂不创建对象
        Student desiredStudent = null;
        String sql_select_id = "SELECT * FROM actor WHERE id = ?";
        PreparedStatement pstmt_select_id =
                connection.prepareStatement(sql_select_id);
        pstmt_select_id.setInt(1, id);
        ResultSet resultSet_select_id = pstmt_select_id.executeQuery();
        //如果表中存在id对应的记录，则获得表中的字段值，并创建对象
        if (resultSet_select_id.next()) {
            String name = resultSet_select_id.getString("name");
            String no = resultSet_select_id.getString("no");
            String remarks = resultSet_select_id.getString("remarks");
            desiredStudent = new Student(name,no,remarks);
            desiredStudent.setId(id);
        }
        //关闭ResultSet, statement资源
        JdbcHelper.close(resultSet_select_id, pstmt_select_id);
        return desiredStudent;
    }

    public boolean update(Teacher actor) throws SQLException {
        actors.remove(actor);
        return actors.add(actor);
    }

    /**
     * 由于Teacher扩展了Actor，前者对象id取后者的id的值。
     * Actor表只有id一个字段，该字段自动生成。
     * 用于事务。调用本方法的Service，在调用本方法后，还会调用UserDao.add方法
     * 两次对Dao的调用要保证是同一个Connection对象，才能将它们设置在同一事务中
     *
     * @param connection 来自Service的Connection对象
     * @return 成功增加：新对象的id，失败：0
     * @throws SQLException
     */
    public int add( Connection connection) throws SQLException {
        //根据连接对象准备语句对象，如果SQL语句为多行，注意语句不同部分之间要有空格
        PreparedStatement preparedStatement_actor =
                connection.prepareStatement(
                        "INSERT INTO actor " +
                                " ()" +
                                " VALUES ()", Statement.RETURN_GENERATED_KEYS);//要求返回主键集合
        //执行预编译语句，用其返回值、影响的行数为赋值affectedRowNum
        int affectedRowNum = preparedStatement_actor.executeUpdate();
        int idOfAddedActor = 0;
        ResultSet resultSet = preparedStatement_actor.getGeneratedKeys();//获得生成的主键集合
        if (resultSet.next()) {
            idOfAddedActor = resultSet.getInt(1);//读取第一个主键
        }
        //本方法是事务的一部分，不能释放资源
        JdbcHelper.close(preparedStatement_actor,null);
        //新增元组的id
        return idOfAddedActor;

    }

    public boolean delete(Integer id, Connection connection) throws SQLException {
        Student actor = this.find(id, connection);
        return this.delete(actor);
    }

    public boolean delete(Student actor) throws SQLException {
        return actors.remove(actor);
    }


}
